﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Linq;
using System.Configuration;

using BuzzwordBingo.Interface;
using BuzzwordBingo.Entities;

namespace BuzzwordBingo.PersistenceLINQ
{
    /// <summary>
    /// Persistence Service using LINQtoSQL, implements IPersistenceService
    /// </summary>
    public class PersistenceService : IPersistenceService
    {
        #region data context

        private static BuzzwordBingoDbDataContext DataContext
        {
            get
            {
                return new BuzzwordBingoDbDataContext(ConfigurationManager.ConnectionStrings["BuzzwordBingoDb"].ConnectionString);
            }
        }

        #endregion
        
        #region IPersistenceService Members

        public IBingoEvent LoadEvent(int EventId)
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                var theEvent = context.BingoEvents.SingleOrDefault(e => e.EventId == EventId);
                var participants = context.Participants.Where(p => p.EventId == EventId);
                foreach (var p in participants)
                    theEvent.Participants.Add(p);

                IBingoEvent be = new BuzzwordBingo.Entities.BingoEvent()
                {
                    Id = theEvent.EventId,
                    StartDate = theEvent.StartDate,
                    EndDate = theEvent.EndDate,
                    Organiser = theEvent.Organiser,
                    Speaker = theEvent.Speaker
                };

                foreach (var p in theEvent.Participants)
                {
                    IParticipant participant = new BuzzwordBingo.Entities.Participant(p.Email)
                    {
                        Id = p.ParticipantId
                    };
                    be.ParticipantList.Add(participant);
                }

                if (theEvent.BuzzwordCategoryId.HasValue)
                    be.Category = LoadBuzzwordCategory(theEvent.BuzzwordCategoryId.Value);

                return be;
            }
        }

        public void SaveEvent(IBingoEvent Event)
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                BuzzwordBingo.Entities.BingoEvent theEvent = (BuzzwordBingo.Entities.BingoEvent)Event;

                // update bingoevent
                var newEvent = context.BingoEvents.SingleOrDefault(c => c.EventId == theEvent.Id);

                if (newEvent == null)
                {
                    // create new event
                    newEvent = new BingoEvent()
                    {
                        EventId = theEvent.Id,
                        BuzzwordCategoryId = theEvent.Category.GetId,
                        StartDate = theEvent.StartDate,
                        EndDate = theEvent.EndDate,
                        Speaker = theEvent.Speaker,
                        Organiser = theEvent.Organiser
                    };
                    context.BingoEvents.InsertOnSubmit(newEvent);
                }
                else
                {
                    // update events
                    newEvent.BuzzwordCategoryId = theEvent.Category.GetId;
                    newEvent.StartDate = theEvent.StartDate;
                    newEvent.EndDate = theEvent.EndDate;
                    newEvent.Speaker = theEvent.Speaker;
                    newEvent.Organiser = theEvent.Organiser;
                }

                // update participants
                foreach (BuzzwordBingo.Entities.Participant p in theEvent.ParticipantList)
                {
                    var newParticipant = context.Participants.SingleOrDefault(c => c.ParticipantId == p.Id);

                    if (newParticipant == null)
                    {
                        // create a new participant
                        newParticipant = new Participant()
                            {
                                ParticipantId = p.Id,
                                EventId = theEvent.Id,
                                Email = p.Email
                            };
                        context.Participants.InsertOnSubmit(newParticipant);
                    }
                    else
                    {
                        // update participant
                        newParticipant.ParticipantId = p.Id;
                        newParticipant.EventId = theEvent.Id;
                        newParticipant.Email = p.Email;
                    }
                }

                try
                {
                    context.SubmitChanges(ConflictMode.ContinueOnConflict);
                }
                catch (ChangeConflictException)
                {
                    context.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
                }
            }
        }

        public IList<IBuzzwordCategory> LoadBuzzwordCategoryList()
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                var myCategoryList = from c in context.BuzzwordCategories
                                     select c;

                List<IBuzzwordCategory> categories = new List<IBuzzwordCategory>();

                foreach (BuzzwordCategory bcl in myCategoryList)
                    categories.Add(new BuzzwordBingo.Entities.BuzzwordCategory(bcl.BuzzwordCategoryId , bcl.Category));

                return categories;
            }
        }

        public IBuzzwordCategory LoadBuzzwordCategory(int BuzzwordCategoryId)
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                //var bcl = context.BuzzwordCategoryLists.Where(l => l.BuzzwordCategoryId == BuzzwordCategoryId).OrderBy(b => b.Buzzword.FuzzyWord);

                var myCategory = from c in context.BuzzwordCategories 
                                 where c.BuzzwordCategoryId == BuzzwordCategoryId 
                                 select c;

                BuzzwordBingo.Entities.BuzzwordCategory category = new BuzzwordBingo.Entities.BuzzwordCategory(myCategory.First().BuzzwordCategoryId, myCategory.First().Category);

                var myList = from b in context.Buzzwords
                             where b.BuzzwordCategory_Buzzwords.Any(l => l.BuzzwordCategoryId == BuzzwordCategoryId)
                             orderby b.FuzzyWord
                             select b;

                //List<IBuzzWord> buzzList = new List<IBuzzWord>();
                //foreach (BuzzwordCategoryList list in bcl)
                //    buzzList.Add(new BuzzwordBingo.Entities.BuzzWord(list.Buzzword.FuzzyWord));

                foreach (Buzzword b in myList)
                    category.BuzzWordList.Add(new BuzzwordBingo.Entities.BuzzWord(b.FuzzyWord));

                return category;
            }
        }

        #endregion
    }
}
